# DB-API 2.0 interface for SQLite databases
import sqlite3
import os
# global var
# 数据库文件绝句路径
# import cx_Oracle
import pymysql
import logging.config
import traceback
import logging.config

from comm.cmomon import IP_ADDRESS, USER_NAME, USER_PASSWORLD, IP_PORT, DATEBASE
print(os.path.join(os.path.dirname(os.path.abspath(__file__)), "config\\logging.ini"))
logging.config.fileConfig(os.path.join(os.path.dirname(os.path.abspath(__file__)), "config\\logging.ini"))
logger = logging.getLogger("infoLogger")

# 是否打印sql
SHOW_SQL = False


def get_conn():
    '''获取到数据库的连接对象，参数为数据库文件的绝对路径
    如果传递的参数是存在，并且是文件，那么就返回硬盘上面改
    路径下的数据库文件的连接对象；否则，返回内存中的数据接
    连接对象'''
    print(DB_FILE_PATH)
    conn = sqlite3.connect(DB_FILE_PATH)
    if os.path.exists(DB_FILE_PATH) and os.path.isfile(DB_FILE_PATH):
        if SHOW_SQL:
            print('硬盘上面:[{}]'.format(DB_FILE_PATH))
        return conn
    else:
        conn = None
        if SHOW_SQL:
            print('内存上面:[:memory:]')
        return sqlite3.connect(':memory:')


def get_cursor(conn):
    '''该方法是获取数据库的游标对象，参数为数据库的连接对象
    如果数据库的连接对象不为None，则返回数据库连接对象所创
    建的游标对象；否则返回一个游标对象，该对象是内存中数据
    库连接对象所创建的游标对象'''
    if conn is not None:
        return conn.cursor()
    else:
        return get_conn('').cursor()


def close_all(conn, cu):
    '''关闭数据库游标对象和数据库连接对象'''
    try:
        if cu is not None:
            cu.close()
    finally:
        if cu is not None:
            cu.close()


def save(sql, data):
    conn = get_conn()
    '''插入数据'''
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}],参数:[{}]'.format(sql, d))
                print(sql.format(d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def fetchall(sql):
    conn = get_conn()
    '''查询所有数据'''
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        if SHOW_SQL:
            print('执行sql:[{}]'.format(sql))
        cu.execute(sql)
        return cu.fetchall()
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def fetchone(sql, data):
    conn = get_conn()
    '''查询一条数据'''
    if sql is not None and sql != '':
        if data is not None:
            # Do this instead
            d = (data)
            cu = get_cursor(conn)
            if SHOW_SQL:
                print('执行sql:[{}],参数:[{}]'.format(sql, data))
            cu.execute(sql, d)
            return cu.fetchall()
        else:
            print('the [{}] equal None!'.format(data))
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def action_fetchone(sql, data):
    conn = get_conn()
    '''查询一条数据'''
    if sql is not None and sql != '':
        if data is not None:
            # Do this instead
            d = (data)
            cu = get_cursor(conn)
            if SHOW_SQL:
                print('执行sql:[{}],参数:[{}]'.format(sql, data))
            cu.execute(sql, d)
            return cu.fetchone()
        else:
            print('the [{}] equal None!'.format(data))
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def update(sql, data):
    conn = get_conn()
    '''更新数据'''
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}],参数:[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def delete(sql, data):
    conn = get_conn()
    '''删除数据'''
    if sql is not None and sql != '':
        if data is not None:
            cu = get_cursor(conn)
            for d in data:
                if SHOW_SQL:
                    print('执行sql:[{}],参数:[{}]'.format(sql, d))
                cu.execute(sql, d)
                conn.commit()
            close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))


def truncate(sql):
    conn = get_conn()
    '''删除数据'''
    if sql is not None and sql != '':
        cu = get_cursor(conn)
        if SHOW_SQL:
            print('执行sql:[{}]'.format(sql))
        cu.execute(sql)
        conn.commit()
        close_all(conn, cu)
    else:
        print('the [{}] is empty or equal None!'.format(sql))


# class ConnOracle():
#     '''
#     mysql 数据库
#     '''
#     def __init__(self):
#         pass
#     def get_conn_cursor(self):
#         Conn = cx_Oracle.connect(USER_NAME,USER_PASSWORLD,)
#         self.cur = Conn.cursor()
#
#     def get_fechone(self,sql,data):
#         '''查询一条数据'''
#         if hasattr(self,"cur"):
#             if sql is not None and sql != '':
#                 if data is not None:
#                     # Do this instead
#                     d = (data)
#                     if SHOW_SQL:
#                         print('执行sql:[{}],参数:[{}]'.format(sql, data))
#                     self.cur.execute(sql, d)
#                     return self.cur.fetchall()
#                 else:
#                     print('the [{}] equal None!'.format(data))
#             else:
#                 print('the [{}] is empty or equal None!'.format(sql))
#         pass
#
#     def get_fechall(self,sql):
#         '''查询所有数据'''
#         if hasattr(self, "cur"):
#             if sql is not None and sql != '':
#                 if SHOW_SQL:
#                     print('执行sql:[{}]'.format(sql))
#                 self.cur.execute(sql)
#                 return self.cur.fetchall()
#             else:
#                 print('the [{}] is empty or equal None!'.format(sql))
#         pass
#
#     def update_date(self):
#         pass
#
#     def action_fetchone(self,sql, data):
#         '''查询一条数据'''
#         if hasattr(self, "cur"):
#             if sql is not None and sql != '':
#                 if data is not None:
#                     d = (data)
#                     if SHOW_SQL:
#                         print('执行sql:[{}],参数:[{}]'.format(sql, data))
#                     self.cur.execute(sql, d)
#                     return self.cur.fetchone()
#                 else:
#                     print('the [{}] equal None!'.format(data))
#             else:
#                 print('the [{}] is empty or equal None!'.format(sql))


class ConnMySQL():
    '''
    mysql 数据库
    '''

    def __init__(self):
        self.Conn = pymysql.connect(host=IP_ADDRESS, user=USER_NAME, password=USER_PASSWORLD,
                                    database=DATEBASE, port=IP_PORT, charset='utf8')
        self.cur = self.Conn.cursor()

    def fetchone(self, sql, data):
        '''查询一条数据'''
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    if data is not None:
                        # Do this instead
                        d = (data)
                        if SHOW_SQL:
                            print('执行sql:[{}],参数:[{}]'.format(sql, data))
                        self.cur.execute(sql, d)
                        return self.cur.fetchall()
                    else:
                        print('the [{}] equal None!'.format(data))
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = pymysql.connect(host=IP_ADDRESS, user=USER_NAME, password=USER_PASSWORLD,
                                            database=DATEBASE, port=IP_PORT, charset='utf8')
                self.cur = self.Conn.cursor()
        except Exception:
            logging.error(traceback.format_exc())

        pass

    def get_fechall(self, sql):
        '''查询所有数据'''
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    if SHOW_SQL:
                        print('执行sql:[{}]'.format(sql))
                    self.cur.execute(sql)
                    return self.cur.fetchall()
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = pymysql.connect(host=IP_ADDRESS, user=USER_NAME, password=USER_PASSWORLD,
                                            database=DATEBASE, port=IP_PORT, charset='utf8')
                self.cur = self.Conn.cursor()
        except Exception:
            logging.error(traceback.format_exc())

        pass

    def update_date(self, sql, data):
        '''更新数据'''
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    if data is not None:
                        for d in data:
                            self.Conn.begin()
                            self.cur.execute(sql, d)
                            self.Conn.commit()
                        self.close_all()
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = pymysql.connect(host=IP_ADDRESS, user=USER_NAME, password=USER_PASSWORLD,
                                            database=DATEBASE, port=IP_PORT, charset='utf8')
                self.cur = self.Conn.cursor()
        except Exception:
            self.Conn.rollback()
            logging.error(traceback.format_exc())

    def action_fetchone(self, sql, data):
        '''查询一条数据'''
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    if data is not None:
                        d = (data)
                        if SHOW_SQL:
                            print('执行sql:[{}],参数:[{}]'.format(sql, data))
                        self.cur.execute(sql, d)
                        return self.cur.fetchone()
                    else:
                        print('the [{}] equal None!'.format(data))
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = pymysql.connect(host=IP_ADDRESS, user=USER_NAME, password=USER_PASSWORLD,
                                            database=DATEBASE, port=IP_PORT, charset='utf8')
                self.cur = self.Conn.cursor()
        except Exception:
            logging.error(traceback.format_exc())

    def demo_fetchone(self, sql):
        '''查询一条数据'''
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    self.cur.execute(sql)
                    return self.cur.fetchone()
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = pymysql.connect(host=IP_ADDRESS, user=USER_NAME, password=USER_PASSWORLD,
                                            database=DATEBASE, port=IP_PORT, charset='utf8')
                self.cur = self.Conn.cursor()
        except Exception:
            logging.error(traceback.format_exc())

    def save(self, sql, data):
        '''插入数据'''
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    if data is not None:
                        for d in data:
                            self.Conn.begin()
                            self.cur.execute(sql, d)
                            self.Conn.commit()
                        self.close_all()
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = pymysql.connect(host=IP_ADDRESS, user=USER_NAME, password=USER_PASSWORLD,
                                            database=DATEBASE, port=IP_PORT, charset='utf8')
                self.cur = self.Conn.cursor()
        except Exception:
            self.Conn.rollback()
            logging.error(traceback.format_exc())

    def delete(self, sql, data):
        '''删除数据'''
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    if data is not None:
                        for d in data:
                            self.Conn.begin()
                            self.cur.execute(sql, d)
                            self.Conn.commit()
                        self.close_all()
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = pymysql.connect(host=IP_ADDRESS, user=USER_NAME, password=USER_PASSWORLD,
                                            database=DATEBASE, port=IP_PORT, charset='utf8')
                self.cur = self.Conn.cursor()
        except Exception:
            self.Conn.rollback()
            logging.error(traceback.format_exc())

    def close_all(self):
        '''关闭数据库游标对象和数据库连接对象'''
        try:
            if hasattr(self, "cur"):
                self.cur.close()
        finally:
            if hasattr(self, "Conn"):
                self.Conn.close()

    def insert_values(self, sql, list):
        '''
        批量插入
        :return:
        '''
        try:
            if hasattr(self, "cur"):
                if sql is not None and sql != '':
                    if len(list) != 0:
                        self.Conn.begin()
                        self.cur.executemany(sql, list)
                        self.Conn.commit()
                        affected_rows = self.cur.rowcount
                    self.close_all()
                else:
                    print('the [{}] is empty or equal None!'.format(sql))
            else:
                self.Conn = pymysql.connect(host=IP_ADDRESS, user=USER_NAME, password=USER_PASSWORLD,
                                            database=DATEBASE, port=IP_PORT, charset='utf8')
                self.cur = self.Conn.cursor()
            return affected_rows
        except Exception as f:
            self.Conn.rollback()
            logging.error(traceback.format_exc())

